package com.chima.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.chima.domain.Department;
import com.chima.domain.DepartmentCoffer;
import com.chima.domain.Employees;
import com.chima.domain.HsCheckDepartment;
import com.chima.domain.Performance;
import com.chima.domain.Position;
public class EmployeeDao extends HibernateDaoSupport{
	
	
	// 登陆方法
	public Employees findEmployeesBypwdAndName(String name, String password) {
		Session session=this.getSession();
		Employees e = (Employees)session.createQuery("from Employees e where e.name = '"+name+"' and e.password = '"+password+"' ").uniqueResult();
		super.releaseSession(session);
		return e;
	
	}
	//分页查询
		public List<Employees> findByCriteria(DetachedCriteria criteria, int firstResult,int maxResults) {
			return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
		}
	   //查找总页数
		public int findByTotalCount(int depart) {
			 long temp = 547723223132L;
				if(depart==0){
					List list= this.getHibernateTemplate().find("select count(*) from Employees");
					 temp = (Long) list.get(0);
				}else{
					List list= this.getHibernateTemplate().find("select count(*) from Employees e where e.department.id='"+depart+"' ");
					 temp = (Long) list.get(0);
				}

			return (int) temp;
		}

		public void save( Employees  employees) {
			this.getHibernateTemplate().save( employees);
		}
		public void update(Employees  employees) {
			this.getHibernateTemplate().update( employees);
		}
		
		public void delete( int id) {
			this.getHibernateTemplate().delete( this.getHibernateTemplate().load(Employees.class, id));
			
		}
		public Employees findByName( String name) {
			Session session=this.getSession();
			Employees e = (Employees)session.createQuery("from Employees e where e.name = ?").setParameter(0, name).uniqueResult();
			super.releaseSession(session);
			return e;
			
		}
		public Employees findById( int id ) {
			Session session   =this.getSession();
			Employees e = (Employees)session.createQuery("from Employees e where e.id = ?").setParameter(0, id).uniqueResult();
			super.releaseSession(session);
			return e;
			
		}
		public List<Employees> findByDepartment(  int id ) {
			Session session   =this.getSession();
			List<Employees> e = (List<Employees>)session.createQuery("from Employees e where e.department.id = ?").setParameter(0, id).list();
			super.releaseSession(session);
			return e;
			
		}
		
		public void addRole(int emp,int role,String datapermision) {
			String hql=" update Employees e set e.rose.id=?,e.dataPermission=? where e.id=?";
			Session session   =this.getSession();
			this.getSession().createQuery(hql).setParameter(0,role ).setParameter(1,datapermision).setParameter(2,emp ).executeUpdate();
			super.releaseSession(session);
		}
		
		public List<Employees> findALL() {
			Session session   =this.getSession();
			List<Employees> e = (List<Employees>)session.createQuery("from Employees where department.id is not null").list();
			super.releaseSession(session);
			return e;
			
		}
		public Employees findByNameAndDpepart( String name,Department d) {
			
			Session session   =this.getSession();
			Employees e = (Employees)session.createQuery("from Employees e where e.name = ? and e.department=? ").setParameter(0, name).setParameter(1, d).uniqueResult();
			super.releaseSession(session);
			return e;
		}
		public Department findDeptById(int id) {
			
			Session session   =this.getSession();
			Department e = (Department)session.createQuery("from Department e where e.id = ?  ").setParameter(0, id).uniqueResult();
			super.releaseSession(session);
			return e;
		}
		public List<Employees> findByPosition(  int position) {
			Session session   =this.getSession();
			List<Employees> e = (List<Employees>)session.createQuery("from Employees e where e.position = ?").setParameter(0, position).list();
			super.releaseSession(session);
			return e;
		}
		public List<Employees> findMiddleEmp(  int position,String datapermission) {
			List<Employees> employees =new ArrayList<Employees>();
			Session session   =this.getSession();
			List<Object[]>  e = session.createSQLQuery("SELECT name, deId, finaceCoffe from t_employees e where e.position = ? and e.deId in ("+datapermission+") ").setParameter(0, position).list();
			if(e.size()>0){
				for(int i=0;i<e.size();i++){
					Employees ee= new Employees();
					ee.setName(e.get(i)[0].toString());
					ee.setDepartment(findDeptById((Integer) e.get(i)[1]));
					ee.setFinaceCoffe(Double.valueOf(e.get(i)[2].toString()));
					employees.add(ee);
				}
			}	
			super.releaseSession(session);
			return employees;
			
		}
		public List<Employees> findByDepartmentAndPosition(  int dept,int position ) {
			Session session   =this.getSession();
			List<Employees> e = (List<Employees>)session.createQuery("from Employees e where e.department.id = ? and position=?").setParameter(0, dept).setParameter(1,position).list();
			super.releaseSession(session);
			return e;
			
		}
		public Employees findempByDepartmentAndPosition(  int dept,int position ) {
			Session session   =this.getSession();
			Employees e = (Employees)session.createQuery("from Employees e where e.department.id = ? and position=?").setParameter(0, dept).setParameter(1,position).uniqueResult();
			super.releaseSession(session);
			return e;
			
		}
		//普通员工总人数
		public int findGeneralByTotalCount(int depart) {
			 long temp = 547723223132L;
		    if(depart==0){
		    	 List list= this.getHibernateTemplate().find("select count(*) from Employees e where  e.position=1 ");
				 temp = (Long) list.get(0);
		    }else{
		    	 List list= this.getHibernateTemplate().find("select count(*) from Employees e where e.department.id='"+depart+"' and e.position=1 ");
				 temp = (Long) list.get(0);
		    }
			

			return (int) temp;
		}
		public void addEMPRole(int emp,int role) {
			String hql=" update Employees e set e.rose.id=? where e.id=?";
			Session session   =this.getSession();
			this.getSession().createQuery(hql).setParameter(0,role ).setParameter(1,emp ).executeUpdate();
			super.releaseSession(session);
		}
		public List<Position> ListallPosition() {
			
			Session session   =this.getSession();
			List<Position> e =session.createQuery("from Position").list();
			super.releaseSession(session);
			return e;
		}
		public void updatePosition(int id, double coffer) {
			String hql=" update Position e set e.coffer=? where e.id=?";
			Session session   =this.getSession();
			this.getSession().createQuery(hql).setParameter(0,coffer ).setParameter(1, id).executeUpdate();
			super.releaseSession(session);
		}
		public List<Department> finddepartmentByflag1(int i) {
			Session session   =this.getSession();
			List<Department> e =session.createQuery("from Department where flag=? ").setParameter(0, i).list();
			super.releaseSession(session);
			return e;
		}
		public Double findtotalRewardbydateanddeptname(String searchdate,
				String name) {
			
		   double a=0.0;
			Session session   =this.getSession();
			String d="SELECT a.deptFinace FROM `t_departmentfinace` a where a.department=? and a.date=?";
			Object deptId=this.getSession().createSQLQuery( d).setParameter(0, name).setParameter(1, searchdate).uniqueResult();
			if(deptId!=null){
				
					a=Double.parseDouble(deptId.toString());
				
		}
			return a;
		}
		public List<HsCheckDepartment> findHsCheckDepartmentBymonthandflag(
				String searchdate1, int i) {
			Session session = super.getSession();
			List<HsCheckDepartment> d = session.createQuery("from HsCheckDepartment where monthdate=? and flag=? " ).setParameter(0, searchdate1).setParameter(1, i).list();
			super.releaseSession(session);
			return d;
		}
		public List<HsCheckDepartment> findHsCheckDepartmentBymonth(
				String searchdate1) {
			Session session = super.getSession();
			List<HsCheckDepartment> d = session.createQuery("from HsCheckDepartment where monthdate=? " ).setParameter(0, searchdate1).list();
			super.releaseSession(session);
			return d;
		}
		public void updateHsCheckDepartmentByMonth(String searchdate1) {
			String  sql="update HsCheckDepartment a set a.flag=0 where a.flag=1 and a.monthdate =?";
			Session session = super.getSession();
			 Query query = session.createQuery(sql);
			 query.setParameter(0, searchdate1);
			 query.executeUpdate();
		    super.releaseSession(session);
		}
		public void deleteEmpMoneyBydeptidAndDate(int deptid, String searchdate1) {
			String  sql="DELETE  FROM Performance a  where  a.department.id=? and a.date=?";
			Session session = super.getSession();
			 Query query = session.createQuery(sql);
			 query.setParameter(0, deptid);
			 query.setParameter(1, searchdate1);
			 query.executeUpdate();
		    super.releaseSession(session);
			
		}
		public void saveorupdateEmpmoney(Performance performance) {
			// TODO Auto-generated method stub
			Session session = super.getSession();
			session.save(performance);
		    super.releaseSession(session);
		}
		public void addorupdateHsCheckDepartment(
				HsCheckDepartment hsCheckDepartment) {
			Session session = super.getSession();
			HsCheckDepartment a = (HsCheckDepartment) session.createQuery("from HsCheckDepartment  where deptid=? and monthdate=?").setParameter(0, hsCheckDepartment.getDeptid()).setParameter(1,hsCheckDepartment.getMonthdate()).uniqueResult();
			if (a!=null) {
				String sql1="UPDATE t_hscheckdepartment  SET flag=? WHERE id=?";
				SQLQuery sQLQuery=(SQLQuery) session.createSQLQuery(sql1).setParameter(0, hsCheckDepartment.getFlag()).setParameter(1, a.getId());
				sQLQuery.executeUpdate();
			}else {
				session.save(hsCheckDepartment);
			}
			super.releaseSession(session);
			
		}
		public int findrates(int deptid, String searchdate1) {
	    double a=0.0;
		String sql="SELECT COUNT(a.id) FROM `t_rate` a where a.date=? and a.deID=?";
		Session session = super.getSession();
		Object obj = session.createSQLQuery(sql).setParameter(0, searchdate1).setParameter(1, deptid).uniqueResult();
		super.releaseSession(session);
		if (obj!=null) {
			
			a=Integer.parseInt(obj.toString());
		}
		
			return (int) a;
		}
		public double findtotalkaoping(int searchdepartment, String searchdate) {
			double  a=0.0;
			String sql="SELECT SUM(a.grade) FROM `t_rate` a  ,t_employees e  where a.deID=? and a.date=? and e.position!=2 and a.empID=e.id; ";
			Session session = super.getSession();
			Object obj = session.createSQLQuery(sql).setParameter(0, searchdepartment).setParameter(1, searchdate).uniqueResult();
			super.releaseSession(session);
			if (obj!=null) {
				a=Double.parseDouble(obj.toString());
			}
			return a;
		}
		public Department finddepartmentByid(int searchdepartment) {
			Session session = super.getSession();
			Department d = (Department) session.createQuery("from Department where id=? " ).setParameter(0, searchdepartment).uniqueResult();
			super.releaseSession(session);
			return d;
		}
		public DepartmentCoffer findDepartmentCofferbydeptname(String name) {
			Session session = super.getSession();
			DepartmentCoffer d = (DepartmentCoffer) session.createQuery("from DepartmentCoffer where department=? " ).setParameter(0, name).uniqueResult();
			super.releaseSession(session);
			return d;
		}
		public double findtotalPostCompetent(int searchdepartment,
				String searchdate) {
			double  a=0.0;
			String sql="SELECT SUM(e.postCompetent) from t_rate a, t_employees e where a.deID=? and a.date=? and e.postManager=0 and a.empID=e.id; ";
			Session session = super.getSession();
			Object obj = session.createSQLQuery(sql).setParameter(0, searchdepartment).setParameter(1, searchdate).uniqueResult();
			super.releaseSession(session);
			if (obj!=null) {
				a=Double.parseDouble(obj.toString());
			}
			return a;
		}
		public List<Employees> findEmployeesmangerByDeparmet(
				int searchdepartment) {
			Session session = super.getSession();
			List<Employees> d = session.createQuery("from Employees where department.id=? and postManager>0" ).setParameter(0, searchdepartment).list();
			super.releaseSession(session);
			return d;
		}
		public List<Employees> findListzubydeptid(int deptId) {
			Session session = super.getSession();
			List<Employees> d = session.createQuery("from Employees where department.id=? and position=10" ).setParameter(0, deptId).list();
			super.releaseSession(session);
			return d;
		}
		public void addEMPzu(int empID, String name) {
			Session session = super.getSession();
				String sql1="update  t_employees a set  a.zuname=? where a.id=?";
				SQLQuery sQLQuery=(SQLQuery) session.createSQLQuery(sql1).setParameter(0, name).setParameter(1,empID);
				sQLQuery.executeUpdate();
			super.releaseSession(session);
			
		}
}
